package com.geek.spring5work;

import com.geek.JvmWorkApplication;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.sql.*;

/**
 * @Author: lishuangqiang
 * @Date: 2021/12/5
 * @Description:
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = {JvmWorkApplication.class})
public class JdbcCRUD {

    @Test
    public void testAdd() throws SQLException {
        //获取数据库连接
        Connection conn = JDBCUtils.getConnection();
        //通过Connection对象得到Statement
        Statement statement = conn.createStatement();
        //执行增加操作
        String sql = "insert into student values(1,23,'li')";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
    }


    /*
     * 执行查询操作
     */
    @Test
    public void testSelect() throws SQLException {
        //获取数据库连接
        Connection conn = JDBCUtils.getConnection();
        Statement statement = conn.createStatement();
        String sql = "select * from student";
        ResultSet resultSet = statement.executeQuery(sql);

        //解析ResultSet
        //把光标向下移动一行，并判断下一行是否存在
        while (resultSet.next()) {
            //通过列编号来获取该列的值
            int sid = resultSet.getInt(1);
            //通过列名称来获取该列的值
            String sname = resultSet.getString("sname");
            int sage = resultSet.getInt("sage");
            System.out.println(sid + ", " + sname + ", " + sage);
        }

        /*
         * 获取结果集元数据：
         * 1、得到元数据：resultSet.getMetaData(),返回值为ResultSetMetaData;
         * 2、获取结果集列数：int getColumnCount();
         * 3、获取指定列的列名：String getColumnName(int colIndex)
         */
        int count = resultSet.getMetaData().getColumnCount();
        while (resultSet.next()) {  //遍历行
            for (int i = 1; i <= count; i++) {
                System.out.print(resultSet.getObject(i));
                if (i < count) {
                    System.out.print(", ");
                }
            }
            System.out.println();
        }
        JDBCUtils.close(resultSet, statement, conn);
    }

    @Test
    public void testUpdate() throws SQLException {
        //获取数据库连接
        Connection conn = JDBCUtils.getConnection();
        //通过Connection对象得到Statement
        Statement statement = conn.createStatement();
        //执行修改操作
        String sql = "update student set sname = 'tom', sage = 24  where sid = 7";
        int i = statement.executeUpdate(sql);
        JDBCUtils.close(statement, conn);

        System.out.println(i);
    }

    @Test
    public void testDelete() throws SQLException {
        //获取数据库连接
        Connection conn = JDBCUtils.getConnection();
        //通过Connection对象得到Statement
        Statement statement = conn.createStatement();
        //执行删除操作
        String sql = "delete from student where sid = 7";
        int i = statement.executeUpdate(sql);
        JDBCUtils.close(statement, conn);
        System.out.println(i);
    }


    @Test
    public void testBatch() throws SQLException {
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into student values(?,?,?)";
            statement = conn.prepareStatement(sql);
            //添加参数
            for (int i = 201; i < 203; i++) {
                statement.setInt(1, i + 1);
                statement.setInt(2, i);
                statement.setString(3, "tom" + i);
                //添加批，把这一组参数添加到集合中
                statement.addBatch();
            }
            long start = System.currentTimeMillis();
            statement.executeBatch();  //执行批
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("执行批一共花了：" + (end - start) + "毫秒！");
        } catch (Exception e) {
            conn.rollback();
            throw new RuntimeException(e);
        } finally {
            try {
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Test
    public void testBatch2() throws SQLException {
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            String sql = "insert into geek_order (order_status,pay_status,pay_amount,buyer_name,buyer_phone,buyer_address,create_at,update_at) VALUES(?,?,?,?,?,?,?,?) ";
//            String sql = "insert into student values(?,?,?)";
            statement = conn.prepareStatement(sql);
            //添加参数
            for (int i = 1; i < 1000000; i++) {
                statement.setInt(1, 0);
                statement.setInt(2, 0);
                statement.setInt(3, 1);
                statement.setString(4, "buyer" + i);
                statement.setString(5, "'1234567890");
                statement.setString(6, "'address");
                statement.setString(7, "2021-12-19 14:43:26");
                statement.setString(8, "2021-12-19 14:43:26");
                //添加批，把这一组参数添加到集合中
                statement.addBatch();
            }
            long start = System.currentTimeMillis();
            statement.executeBatch();  //执行批
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println("执行批一共花了：" + (end - start) + "毫秒！");
        } catch (Exception e) {
            conn.rollback();
            throw new RuntimeException(e);
        } finally {
            try {
                if (statement != null) statement.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

}
